终于打卡结束：MySQL经典50题
公众号：尤而小屋
作者：Peter
编辑：Peter
MySQL50题终于要结束了：今天带来的是最后5道题：第46-50题。主要的知识点：各种时间和日期函数的使用
year()：返回年份date_format(now(), '%Y%m%d')：返回年月日dayofyear()：一年中的第几天weekofyear()：一年中的第几周week()：一年中的第几周month()：返回月份dayofweek()：星期索引，1代表星期1weekday()：星期索引，0代表星期1
5个题目分别是：
查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1 查询本周过生日的学生 查询下周过生日的学生 查询本月过生日的学生 查询下月过生日的学生
题目46
题目需求
查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1
分析过程
1、我们以出生年月日中的年份来计算年龄，通过year()来计算当前年份和出生年份的差值
2、比较具体的日期和当前日期的大小，使用dayofyear()来确定每个出生日期是处在每年的哪一天；如果出生日期靠后，则说明最近这年还没有达到一岁，减去1
3、 使用case语句来进行判断
参考资料
SQL实现
下面是自己的方法：
-- 自己的方法
select *
,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth)
when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1
else 'other' end as 'age'
from Student;
下面是参考的方法：
select
s_name
,s_birth
,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1
from Student;
如何返回年份/日期
通过date_format函数能够指定返回的数据
-- 两个方法
select year(now());
select date_format(now(), '%Y');
返回具体的日期：
题目47
题目需求
查询本周过生日的学生
分析过程
！！！注意：我们通过week函数返回日期在年份中的所属周数
select week(now()); -- 47
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)，符合国内标准
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)，国外标准
SQL实现
下面是自己的方法：
select *
from Student
where week(s_birth) = week(now());
下面是参考的方法：
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth); -- 方式1
select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d')); -- 方式2
题目48
题目需求
查询下周过生日的学生
分析过程
本题和上面的题目是类似的，只是需要我们在现有的日期往前推一周
SQL实现
-- 自己的方法
select *
from Student
where week(s_birth) = week(now()) + 1; -- 往前推1周
-- 参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth);
边界问题
如果现在刚好的是今年的最后一个周，那么下周就是明年的第一个周，我们如何解决这个问题呢？？改进后的脚本：
-- 自己的方法
select * from Student
where mod(week(now()), 52) + 1 = week(s_birth);
当现在刚好是第52周，那么mod函数的结果是0，则说明出生的月份刚好是明年的第一周
题目49
题目需求
查询本月过生的同学
分析过程
我们通过month()来查询每个日期所在的月份
SQL实现
-- 自己的方法
select *
from Student
where month(s_birth) = month(now());
-- 参考方法
select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);
返回的是空值，是因为数据本身就没有在11月份出生的同学
题目50
题目需求
查询下月过生的同学
分析过程
和上面的题目类似，需要在现有的月份上加1
SQL实现
-- 自己的方法
select * from Student
where month(s_birth) = month(now()) + 1; -- 推迟一个月
-- 参考方法
select * from Student
where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);
边界问题
假设现在是12月份，那么下个月就是明年的1月份，我们如何解决？？？将上面的代码进行改进：
select * from Student
where mod(month(now()),12) + 1 = month(s_birth);
如果现在是12月份，则mod函数的结果是0，说明生日刚好是1月份
推荐阅读
尤而小屋，一个温馨的小屋。小屋主人，一手代码谋求生存，一手掌勺享受生活，欢迎你的光临